Relational database management systems provide data row storage options that allow data row storage to be varied from a selected native key sequence. Various data storage options may be utilized, for example, “clustered” (which attempts to place the data rows in native key sequence but allows some deviation if space becomes a concern), “sequential” (which places new data rows at the end of the existing space and when space is exhausted, wraps back to the front of a table and begins adding data rows there), “random” (which places data rows wherever there is available space). The “random” option is most commonly used because it consumes the least amount of resources.
For most indexed access of data rows, the actual data row order in the table has no effect on the performance of the database or the accessing application. However, various applications require access to large segments of data rows using a sequential (read next) process. In these cases, the native key sequence may be selected to access the data rows.
Over a period of time with additions and deletions of data rows from the database table, the data row sequence becomes disorganized. When this disorganization occurs, the application accessing the data rows according to native key sequence slows because the database management system is required to perform substantially more physical IOs to retrieve the data rows in the native key sequence.
In order to re-order the data rows according to the native key sequence, database reorganization processes may be regularly scheduled. However, database reorganizations may be resource-intensive and in some cases unnecessary. In these cases, database resources may be wasted when reorganizing database tables that do not need reorganization. Furthermore, when reorganizations are necessary, oftentimes such reorganizations are not performed due to inaction by system administrators for various reasons. Thus, what is needed is a low cost (resource-wise) way to determine whether at least a portion of a sequential-access database will benefit from reorganization. What is also needed is an ability to specify when the database table should be reorganized and a level of reorganization that should take place. What is further needed is an ability to automatically initiate reorganization of the table when it is determined that the table should be reorganized.
These and other drawbacks exist.